/**
 * @author : River
 * @version : 1.1
 * @time : 2004.04.10
 */
package com.j3.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

import org.apache.log4j.Logger;

import com.j3.db.ConnectionManager;
import com.j3.db.DataSourceUnRegisterException;
import com.j3.db.Db;

public class SqlFunc {

    private static final Logger LOGGER = Logger.getLogger(SqlFunc.class);

    public int executeUpdate(Connection conn, String sqlStr) throws SQLException, DataSourceUnRegisterException {
        try {
            int ok;
            conn.setAutoCommit(false);
            Statement st = conn.createStatement();
            ok = st.executeUpdate(sqlStr);
            if (ok >= 0) {
                conn.commit();
            } else {
                LOGGER.debug(sqlStr);
                conn.rollback();
            }
            st.close();
            return ok;
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public int executeUpdate(Db db, String sqlstr) throws SQLException, DataSourceUnRegisterException {
        if (db == null) {
            return executeUpdate(sqlstr);
        }
        Connection conn = ConnectionManager.getConn(db);
        try {
            return executeUpdate(conn, sqlstr);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public int executeUpdate(String sqlstr) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn();
        try {
            return executeUpdate(conn, sqlstr);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public boolean psQuery(Connection conn, String sqlstr, List<List<Object>> parameters) throws SQLException {
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement(sqlstr);
        try {
            if (parameters != null) {
                for (int i = 0; i < parameters.size(); i++) {
                    List<Object> row = parameters.get(i);
                    for (int n = 0; n < row.size(); n++) {
                        Object obj = row.get(n);
                        ps.setObject(n + 1, obj);
                    }
                    ps.addBatch();
                }
            }
            boolean allok = true;
            int[] results = ps.executeBatch();
            conn.commit();
            for (int i = 0; i < results.length; i++) {
                if (results[i] < 0) {
                    allok = false;
                    break;
                }
            }
            return allok;
        }
        catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }

    public boolean psQuery(Connection conn, String sqlstr, List<List<Object>> parameters, List<Integer> types) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sqlstr);
        try {
            conn.setAutoCommit(false);
            if (parameters != null) {
                for (int i = 0; i < parameters.size(); i++) {
                    List<Object> row = parameters.get(i);
                    for (int n = 0; n < row.size(); n++) {
                        Object obj = row.get(n);
                        ps.setObject(n + 1, obj, types.get(n));
                    }
                    ps.addBatch();
                }
            }
            boolean allok = true;
            int[] results = ps.executeBatch();
            conn.commit();
            for (int i = 0; i < results.length; i++) {
                if (results[i] < 0) {
                    allok = false;
                    break;
                }
            }
            return allok;
        }
        catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }

    public boolean psQuery(Db db, String sqlstr, List<List<Object>> parameters) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn(db);
        try {
            return psQuery(conn, sqlstr, parameters);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public boolean psQuery(Db db, String sqlstr, List<List<Object>> parameters, List<Integer> types) throws SQLException,
            DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn(db);
        try {
            return psQuery(conn, sqlstr, parameters, types);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public boolean psQuery(String sqlstr, List<List<Object>> parameters) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn();
        try {
            return psQuery(conn, sqlstr, parameters);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public String getCol00(Connection conn, String sqlstr) throws SQLException, DataSourceUnRegisterException {
        String firstValue = "";
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(sqlstr);
        while (rset.next()) {
            firstValue = rset.getString(1);
            break;
        }
        rset.close();
        stmt.close();
        return firstValue;
    }

    public String getCol00(Connection conn, String sql, List<Object> parameters) throws SQLException, DataSourceUnRegisterException {
        String firstValue = "";
        PreparedStatement ps = conn.prepareStatement(sql);
        if (parameters != null) {
            for (int i = 0; i < parameters.size(); i++) {
                Object obj = parameters.get(i);
                ps.setObject(i + 1, obj);
            }
            ResultSet rset = ps.executeQuery();
            while (rset.next()) {
                firstValue = rset.getString(1);
                break;
            }
            rset.close();
            ps.close();
        }
        return firstValue;
    }

    public String getCol00(Db db, String sqlstr) throws SQLException, DataSourceUnRegisterException {
        if (db == null) {
            return getCol00(sqlstr);
        }
        Connection conn = ConnectionManager.getConn(db);
        try {
            return getCol00(conn, sqlstr);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public String getCol00(String sqlstr) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn();
        try {
            return getCol00(conn, sqlstr);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public String getCol00(String sqlstr, List<Object> parameters) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn();
        try {
            return getCol00(conn, sqlstr, parameters);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public String getCol00(String sqlstr, Object... parameters) throws SQLException, DataSourceUnRegisterException {
        return getCol00(sqlstr, Arrays.asList(parameters));
    }

    public int psUpdate(Connection conn, String sqlStr, List<Object> parameters) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sqlStr);
        if (parameters != null) {
            int i = 1;
            for (Object parameter : parameters) {
                ps.setObject(i++, parameter);
            }
        }
        return ps.executeUpdate();
    }

    public int psUpdate(Connection conn, String sqlStr, List<Object> parameters, List<Integer> types) throws SQLException {
        if (parameters.size() != types.size()) {
            throw new IllegalArgumentException("Parameters'size should equal to Types' size");
        }
        PreparedStatement ps = conn.prepareStatement(sqlStr);
        if (parameters != null) {
            for (int i = 0; i < parameters.size(); i++) {
                ps.setObject(i + 1, parameters.get(i), types.get(i));
            }
        }
        return ps.executeUpdate();
    }

    public int psUpdate(Db db, String sqlStr, List<Object> parameters) throws SQLException, DataSourceUnRegisterException {
        if (db == null) {
            return psUpdate(sqlStr, parameters);
        }
        Connection conn = ConnectionManager.getConn(db);
        try {
            return psUpdate(conn, sqlStr, parameters);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public int psUpdate(String sqlStr, List<Object> parameters) throws SQLException, DataSourceUnRegisterException {
        Connection conn = ConnectionManager.getConn();
        try {
            return psUpdate(conn, sqlStr, parameters);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public int psUpdate(String sqlStr, List<Object> parameters, List<Integer> types) throws SQLException {
        return psUpdate((Db) null, sqlStr, parameters, types);
    }

    public int psUpdate(Db db, String sqlStr, List<Object> parameters, List<Integer> types) throws SQLException {
        Connection conn = ConnectionManager.getConn(db);
        try {
            return psUpdate(conn, sqlStr, parameters, types);
        }
        finally {
            ConnectionManager.close(conn);
        }
    }

    public int executeInsertAndReturnGeneratedKey(String sqlStr, List<Object> parameters) throws SQLException {
        Connection conn = ConnectionManager.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sqlStr, Statement.RETURN_GENERATED_KEYS);
            if (parameters != null) {
                int i = 1;
                for (Object parameter : parameters) {
                    ps.setObject(i++, parameter);
                }
            }
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            }
            catch (Exception e) {
            }
            ConnectionManager.close(conn);
        }
        return -1;
    }

}